﻿use [UnfriendlyFire];
go

/* Drop views */
if OBJECT_ID(N'RankedOfficers', N'V') is not null
drop view RankedOfficers;
go
if OBJECT_ID(N'AuthoredArmedForces', N'V') is not null
drop view AuthoredArmedForces;
go
if OBJECT_ID(N'AuthoredScenarios', N'V') is not null
drop view AuthoredScenarios;
go

/* Drop indices*/
if OBJECT_ID(N'IX_Officiers_Email', N'I') is not null
drop index IX_Officiers_Email on Users;
go

/* Drop constraints */
if OBJECT_ID(N'OfficerRank', N'F') is not null
alter table Officers drop constraint OfficerRank;
go
if OBJECT_ID(N'TheaterAuthor', N'F') is not null
alter table Theaters drop constraint TheaterAuthor;
go
if OBJECT_ID(N'ArmedForceAuthor', N'F') is not null
alter table ArmedForces drop constraint ArmedForceAuthor;
go
if OBJECT_ID(N'ScenarioAuthor', N'F') is not null
alter table Scenarios drop constraint ScenarioAuthor;
go
if OBJECT_ID(N'UnitTypeWeaponUnitType', N'F') is not null
alter table UnitWeaponTypes drop constraint UnitTypeWeaponUnitType;
go
if OBJECT_ID(N'UnitTypeWeaponWeaponType', N'F') is not null
alter table UnitWeaponTypes drop constraint UnitTypeWeaponWeaponType;
go
if OBJECT_ID(N'UnitWeaponTypePrimaryKey', N'P') is not null
alter table UnitWeaponTypes drop constraint UnitWeaponTypePrimaryKey;
go
if OBJECT_ID(N'ArmedForceParameterScenario', N'F') is not null
alter table ArmedForceParameters drop constraint ArmedForceParameterScenario;
go
if OBJECT_ID(N'SiteParameterScenario', N'F') is not null
alter table SiteParameters drop constraint SiteParameterScenario;
go

/* Drop tables */
if OBJECT_ID(N'Officers', N'U') is not null
drop table Officers;
go
if OBJECT_ID(N'Theaters', N'U') is not null
drop table Theaters;
go
if OBJECT_ID(N'Ranks', N'U') is not null
drop table Ranks;
go
if OBJECT_ID(N'Scenarios', N'U') is not null
drop table Scenarios;
go
if OBJECT_ID(N'ArmedForces', N'U') is not null
drop table ArmedForces;
go
if OBJECT_ID(N'UnitTypes', N'U') is not null
drop table UnitTypes;
go
if OBJECT_ID(N'WeaponTypes', N'U') is not null
drop table WeaponTypes;
go
if OBJECT_ID(N'UnitWeaponTypes', N'U') is not null
drop table UnitWeaponTypes;
go
if OBJECT_ID(N'ArmedForceParameters', N'U') is not null
drop table ArmedForceParameters;
go
if OBJECT_ID(N'SiteParameters', N'U') is not null
drop table SiteParameters;
go


create table Officers (
	Id int IDENTITY(1,1) not null primary key,
	[Timestamp] timestamp not null,
	Email nvarchar(32) not null unique,
	FirstName nvarchar(32) not null,
	LastName nvarchar(32) not null,
	ExperiencePoints int not null,
	Photo nvarchar(1024) not null,
	RankId int not null,
)
go

create table Theaters (
	Id int identity(1,1) not null primary key,
	[Timestamp] timestamp not null,
	Name nvarchar(128) not null,
	[Description] ntext not null,
	Icon nvarchar(1024) not null,
	Longitude real not null,
	Latitude real not null,
	AuthorId int not null,
)
go

create table Ranks (
	Id int identity(1, 1) not null primary key,
	Title nvarchar(32) not null,
	Abbreviation nvarchar(3) not null,
	RequireExperiencePoints int not null,
	Icon nvarchar(1024) not null,
	[Description] ntext not null,
)
go

insert into Ranks(Title, Abbreviation, RequireExperiencePoints, Icon, [Description]) values
	('Private', 'PVT', 100, 'PVT.gif', 'Nothing'),
	('Private 2', 'PV2', 200, 'PV2.gif', 'Nothing'),
	('Private First Class', 'PFC', 300, 'PFC.gif', 'Nothing'),
	('Specialist', 'SPC', 400, 'SPC.gif', 'Nothing'),
	('Corporal', 'CPL', 500, 'CPL.gif', 'Nothing'),
	('Sergeant', 'SGT', 600, 'SGT.gif', 'Nothing'),
	('Staff Sergeant', 'SSG', 700, 'SSG.gif', 'Nothing'),
	('Sergeant First Class', 'SFC', 800, 'SFC.gif', 'Nothing'),
	('Master Sergeant', 'MSG', 900, 'MSG.gif', 'Nothing'),
	('First Sergeant', '1SG', 1000, '1SG.gif', 'Nothing'),
	('Sergeant Major', 'SGM', 1100, 'SGM.gif', 'Nothing'),
	('Command Sergeant Major', 'CSM', 1200, 'CSM.gif', 'Nothing'),
	('Sergeant Major of the Army', 'SMA', 1300, 'SMA.gif', 'Nothing'),
	('Warrant Officer', 'WO1', 1400, 'WO1.gif', 'Nothing'),
	('Chief Warrant Officer 2', 'CW2', 1500, 'CW2.gif', 'Nothing'),
	('Chief Warrant Officer 3', 'CW3', 1600, 'CW3.gif', 'Nothing'),
	('Chief Warrant Officer 4', 'CW4', 1700, 'CW4.gif', 'Nothing'),
	('Chief Warrant Officer 5', 'CW5', 1800, 'CW5.gif', 'Nothing'),
	('Second Lieutenant', '2LT', 1900, '2LT.gif', 'Nothing'),
	('First Lieutenant', '1LT', 2000, '1LT.gif', 'Nothing'),
	('Captain', 'CPT', 3000, 'CPT.gif', 'Nothing'),
	('Major', 'MAJ', 4000, 'MAJ.gif', 'Nothing'),
	('Lieutenant Colonel', 'LTC', 5000, 'LTC.gif', 'Nothing'),
	('Colonel', 'COL', 6000, 'COL.gif', 'Nothing'),
	('Brigadier General', 'BG', 7000, 'BG.gif', 'Nothing'),
	('Major General', 'MG', 8000, 'MG.gif', 'Nothing'),
	('Lieutenant General', 'LTG', 9000, 'LTG.gif', 'Nothing'),
	('General', 'GEN', 10000, 'GEN.gif', 'Nothing'),
	('General of the Army', 'GA', 100000, 'GA.gif', 'Nothing')
go

create table ArmedForces (
	Id int identity(1, 1) not null primary key,
	[Timestamp] timestamp not null,
	Name nvarchar(32) not null,
	[Description] ntext not null,
	[Icon] nvarchar(1024) not null,
	AuthorId int not null,
)
go

create table Scenarios (
	Id int identity(1,1) not null primary key,
	[Timestamp] timestamp not null,
	Name nvarchar(64) not null,
	[Description] ntext not null,
	[Icon] nvarchar(1024) not null,
	AuthorId int not null,
)
go

/* An armed force parameter to a scenario. */
create table ArmedForceParameters (
	Id int identity(1, 1) not null primary key,
	[Timestamp] timestamp not null,
	ScenarioId int not null,
	[Name] nvarchar(64) not null,
	[Description] ntext not null,
)
go

/* An site parameter to a scenario. */
create table SiteParameters (
	Id int identity(1, 1) not null primary key,
	[Timestamp] timestamp not null,
	ScenarioId int not null,
	[Name] nvarchar(64) not null,
	[Description] ntext not null,
)
go

create table UnitTypes (
	Id int identity(1,1) not null primary key,
	[Timestamp] timestamp not null,
	AuthorId int not null,
	Name varchar(64) not null,
	[Description] ntext not null,
	[Icon] nvarchar(1024) not null,
	[Image] nvarchar(1024) not null,
	Strength int not null,
	MinimumSpeed real not null,
	MaximumSpeed real not null,
)
go

create table WeaponTypes (
	Id [int] identity(1,1) not null primary key,
	[Timestamp] timestamp not null,
	AuthorId int not null,
	Name nvarchar(64) not null,
	[Description] ntext not null,
	Icon nvarchar(1024) not null,
	[Image] nvarchar(1024) not null,
	Damage int not null,
	Speed real not null,
	[Range] real not null,
)
go

create table UnitWeaponTypes (
	UnitTypeId int not null,
	WeaponTypeId int not null,
	AuthorId int not null,
	[Timestamp] timestamp not null,
	[Count] int not null,
)
go


/* Create Foreign key constraints */
alter table Officers add constraint OfficerRank foreign key (RankId) references [Ranks](Id);
go
alter table Theaters add constraint TheaterAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table ArmedForces add constraint ArmedForceAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table Scenarios add constraint ScenarioAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table UnitTypes add constraint UnitTypeAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table WeaponTypes add constraint WeaponTypeAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table UnitWeaponTypes add constraint UnitWeaponTypeAuthor foreign key (AuthorId) references [Officers](Id);
go
alter table UnitWeaponTypes add constraint UnitTypeWeaponUnitType foreign key (UnitTypeId) references UnitTypes(id);
go
alter table UnitWeaponTypes add constraint UnitTypeWeaponWeaponType foreign key (WeaponTypeId) references WeaponTypes(id);
go
alter table UnitWeaponTypes add constraint UnitWeaponTypePrimaryKey primary key (UnitTypeId, WeaponTypeId);
go
alter table ArmedForceParameters add constraint ArmedForceParameterScenario foreign key (ScenarioId) references Scenarios(Id);
go
alter table SiteParameters add constraint SiteParameterScenario foreign key (ScenarioId) references Scenarios(Id);
go

/* Create Indices */
create unique index IX_Officiers_Email on Officers(Email);
go

/* Create Views */

create view RankedOfficers 
as 
select o.Email, o.FirstName, o.LastName, o.Photo, r.Title as RankTitle, r.Icon as RankIcon
from Officers as o 
join Ranks as r on o.RankId = r.Id
go

create view AuthoredArmedForces
as 
select a.Id as ArmedForceId, a.[Timestamp], a.Name, a.[Description], a.[Icon], o.Email as AuthorEmail
from ArmedForces as a 
join Officers as o on a.AuthorId = o.Id
go

create view AuthoredScenarios
as 
select s.Id as ScenarioId, s.[Timestamp], s.Name, s.[Description], s.[Icon], o.Email as AuthorEmail
from Scenarios as s 
join Officers as o on s.AuthorId = o.Id
go

create view AuthoredWeaponTypes
as 
select w.Id as WeaponTypeId, w.[Timestamp], w.Name, w.[Description], w.[Icon], o.Email as AuthorEmail
from WeaponTypes as w
join Officers as o on w.AuthorId = o.Id
go
